package cn.edu.nit.sys.mapper;

import cn.edu.nit.sys.dto.UserDTO;
import cn.edu.nit.sys.entity.User;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface UserMapper extends BaseMapper<User> {

    @Select("select username, password, role_id from sys_user where username = #{username}")
    User selectUserUsername(String username);

    @Select("select u.id, u.username, u.avatar, u.role_id, date_format(u.create_time, '%Y-%M-%e %H:%i:%S') as create_time, " +
            "s.name, s.sex, s.dept_id, s.major_id, s.clazz, s.state_code, s.state_value, s.coordinator as teacher_id, " +
            "s.birth, s.nation, s.position, s.area, s.address, s.phone, s.email, u.nickname, s.grade, " +
            "d.name as dept, d.major, " +
            "st.name as teacher " +
            "from sys_user u " +
            "left join sys_student s on u.id = s.uid " +
            "left join sys_teacher t on u.id = t.uid " +
            "left join sys_department d on d.dept_id = s.dept_id and d.major_id = s.major_id " +
            "left join sys_teacher st on st.id = s.coordinator " +
            "where username = #{username}")
    Map<String, ?> selectStudentInfo(String username);

    @Select("select u.id, u.username, u.avatar, u.role_id, u.nickname, " +
            "t.name, t.sex, t.dept_id, t.state_id as state_code, " +
            "t.state_name as state_value, t.birth, t.position, t.phone, " +
            "t.email, date_format(t.create_time, '%Y-%M-%e %H:%i:%S') as create_time, t.join_us, " +
            "d.name as dept " +
            "from sys_user u " +
            "left join sys_teacher t on u.id = t.uid " +
            "left join sys_department d on d.dept_id = t.dept_id " +
            "where username = #{username} limit 1")
    Map<String, ?> selectTeacherInfo(String username);

    @Select("select u.id, u.username, u.role_id, u.avatar, u.nickname, " +
            "st.name, st.phone, st.email, date_format(st.create_time, '%Y-%M-%e %H:%i:%S') as create_time, st.birth " +
            "from sys_user u " +
            "left join sys_staff st on st.id = u.id where username = #{username}")
    Map<String, ?> adminInfo(String username);

    UserDTO login(String name);

    Page<UserDTO> getUserList(Page<UserDTO> page, String name, String role, String id);

    @Update("update sys_user set avatar = #{sourUrl} where id = #{id}")
    Integer changeAvatar(String id, String sourUrl);

    @Update("update sys_student set phone = #{phone} where uid = #{id}")
    int updateStudentPhone(String id, String phone);

    @Update("update sys_teacher set phone = #{phone} where uid = #{id}")
    int updateTeacherPhone(String id, String phone);

    @Update("update sys_user set password = #{password} where username = #{account}")
    void updatePassword(String account, String password);

    @Select("select role_id from sys_user where id = #{id}")
    String getRoleId(String id);

    @Update("update sys_user set avatar = #{avatar}, nickname = #{nickname} where id = #{id}")
    void updateInfo(String id, String avatar, String nickname);


    @Update("update sys_student set email = #{email} where uid = #{id}")
    int updateStudentEmail(String id, String email);

    @Update("update sys_teacher set email = #{email} where uid = #{id}")
    int updateTeacherEmail(String id, String email);

    @Select("select " +
            "ifnull((select count(id) from sys_relation where uid = #{id}), 0) as fans, " +
            "ifnull((select count(id) from sys_relation where fan_id = #{id}), 0) as attention, " +
            "ifnull((select count(id) from sys_forum where status = '2' and user_id = #{id}), 0) as forum, " +
            "ifnull((select count(b.id) from sys_forum a, sys_forum_log b " +
            "where a.id = b.fid and b.type = 2 and a.user_id = #{id}), 0) as likes, " +
            "(select avatar from sys_user where id = #{id}) as avatar, " +
            "(select nickname from sys_user where id = #{id}) as nickname")
    Map<String, Integer> countData(String id);

    @Select("select if(id is not null, true, false) as rs from sys_relation where uid = #{uid} and fan_id = #{id}")
    String checkFans(String uid, String id);

    @Select("select id from sys_user where nickname = #{nickname}")
    String isExist(String nickname);

    @Select("call update_fans(#{primaryId}, #{uid}, #{id})")
    void updateFans(String primaryId, String uid, String id);

    @Select("select avatar, nickname from sys_user where id = #{id}")
    Map<String, String> getUserInfoById(String id);

    @Select("select a.id, a.uid, a.name, b.avatar from sys_student a " +
            "left join sys_user b on a.uid = b.id " +
            "where concat(a.major, a.clazz) = (select concat(major, clazz) from sys_student where uid = #{id}) " +
            "and a.grade = (select grade from sys_student where uid = #{id})")
    List<Map<String,String>> getClazzUser(String id);

    @Select("select b.avatar, b.nickname, b.id from sys_relation a " +
            "left join sys_user b on a.uid = b.id where fan_id = #{id} ")
    Page<Map<String, String>> getAttentionUser(Page<Map<String, String>> page, String id);

    @Select("select b.avatar, b.nickname, b.id from sys_relation a " +
            "left join sys_user b on a.fan_id = b.id where uid = #{id} ")
    Page<Map<String, String>> getFansUser(Page<Map<String, String>> page, String id);
}
